package cn.group_06.bookmanager.dao;

import cn.group_06.bookmanager.model.student;
import cn.group_06.bookmanager.model.User;

import cn.group_06.bookmanager.utils.toolUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.SQLException;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;

public class FbDao {
	//
	
	//
	
	public int addUser(Connection con, student user) throws Exception{
		//鏌ヨ娉ㄥ唽鐢ㄦ埛鍚嶆槸鍚﹀瓨鍦�
		String sql = "select * from student where xsname=? ";

	    sql="insert into student (name,ekcj,pjcj,zhcj,yj,lxr,lxrdh,bz,id) values (?,?,?,?,?,?,?,?,?)";
	    PreparedStatement pstmt2=(PreparedStatement) con.prepareStatement(sql);
		pstmt2.setString(1, user.getName());
		pstmt2.setString(2, String.valueOf(user.getEkcj()));
		pstmt2.setString(3,  String.valueOf(user.getPjcj()) );
		pstmt2.setString(4,  String.valueOf(user.getZhcj()) );
		pstmt2.setString(5,  String.valueOf(user.getEmail()) );
		pstmt2.setString(6,  String.valueOf(user.getLxr()) );
		pstmt2.setString(7,  String.valueOf(user.getLxrphone()) );
		pstmt2.setString(8,  String.valueOf(user.getBz()) );
		pstmt2.setInt(9,  user.getId() );
		return pstmt2.executeUpdate();
	}
	
	
	public ResultSet list(Connection con,student user)throws Exception{
		
		StringBuffer sb=new StringBuffer("select * from student ");

		if(!toolUtil.isEmpty(user.getName())){
			sb.append(" where name like '%"+user.getName()+"%'");
			
		}
		PreparedStatement pstmt=(PreparedStatement) con.prepareStatement(sb.toString());
		return pstmt.executeQuery();
		/*StringBuffer sb=new StringBuffer("select * from student ");

		if(!String.valueOf(user.getId()).equals("null")){
			sb.append(" where id="+user.getId());
		}
		PreparedStatement pstmt=(PreparedStatement) con.prepareStatement(sb.toString());
		return pstmt.executeQuery();*/
	}
	public int update(Connection con,student user) throws Exception{
		String sql="update student set name=?,ekcj=?,pjcj=? ,zhcj=?,yj=?,lxr=?,lxrdh=? ,bz=? where id=?";
		PreparedStatement pstmt2=(PreparedStatement) con.prepareStatement(sql);
		pstmt2.setString(1,String.valueOf(user.getName()) );
		pstmt2.setString(2, String.valueOf(user.getEkcj()));
 pstmt2.setString(3,  String.valueOf(user.getPjcj()));
		 pstmt2.setString(4,  String.valueOf(user.getZhcj()));
		pstmt2.setString(5,  String.valueOf(user.getEmail()) );
		pstmt2.setString(6,  String.valueOf(user.getLxr()) );
		pstmt2.setString(7,  String.valueOf(user.getLxrphone()) );
		pstmt2.setString(8,  String.valueOf(user.getBz()) );
		pstmt2.setInt(9, user.getId());
		return pstmt2.executeUpdate();
	}

	public int del(Connection con,student user)throws Exception{
		String sql="delete from student  where id=?";
		PreparedStatement pstmt=(PreparedStatement) con.prepareStatement(sql);
		pstmt.setInt(1, user.getId());
		return pstmt.executeUpdate();
	}
	


	public void WriteExcel(List<student> list, String filepath) throws IOException {

		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet("瀹㈡埛淇℃伅");
		HSSFRow row = sheet.createRow(0);
		String[] s = { "num", "name", "jc", "address", "phone","email","lxr","lxrdh","bz" };
		for (int i = 0; i < 9; i++) {
			row.createCell(i).setCellValue(s[i]);

		}
		for (int j = 0; j < list.size(); j++) {
			HSSFRow r = sheet.createRow(j + 1);
			r.createCell(0).setCellValue(list.get(j).getId());
			r.createCell(1).setCellValue(list.get(j).getName());
			r.createCell(2).setCellValue(list.get(j).getEkcj());
			r.createCell(3).setCellValue(list.get(j).getPjcj());
			r.createCell(4).setCellValue(list.get(j).getZhcj());
			r.createCell(5).setCellValue(list.get(j).getEmail());
			r.createCell(6).setCellValue(list.get(j).getLxr());
			r.createCell(7).setCellValue(list.get(j).getLxrphone());
			r.createCell(8).setCellValue(list.get(j).getBz());

		}
		OutputStream out = new FileOutputStream(filepath);
		workbook.write(out);
		System.out.println("瀵煎嚭鎴愬姛锛�");
		out.close();
	}
}
